package com.cfs.daoimpl.sys;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.cfs.po.sys.Dict;
import com.cfs.po.sys.KV;

public class DictDao extends Dao {
	
	@Override
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		super.setJdbcTemplate(jdbcTemplate);
//		initDict();
	}
	
	/**
	 * 字典缓存
	 */
	//private static Map<String, Dict> dMap = new HashMap<String, Dict>();

	RowMapper<KV> mapper = new RowMapper<KV>() {
		@Override
		public KV mapRow(ResultSet rs, int arg1) throws SQLException {

			String fldName = rs.getString("NAME");
			KV kv = new KV(rs.getLong("ID"),rs.getInt("EKEY"), rs.getString("EVALUE"));

//			Dict d = dMap.get(fldName);
//			if(d == null){
//				d = new Dict();
//				d.setName(fldName);
//				d.setKvs(new ArrayList<KV>());
//				dMap.put(fldName, d);
//			}
//			d.add(kv);
			
			Dict d = new Dict();
			d.setName(fldName);
			d.setKvs(new ArrayList<KV>());
			d.add(kv);

			return kv;
		}
	};

	/**
	 * 初始化字典
	 * 
	 * @param c
	 * @return
	 */
	public void initDict() {
		//dMap.clear();

		jdbcTemplate.query(DB_SQL.DICT_QUERY_ALL, mapper);
		
		System.out.println("Init Dict: ......OK");
	}
	
	public Dict getByName(String name){
		final Dict d = new Dict();
		d.setName(name);
		System.out.println(name);
		List<KV> list = jdbcTemplate.query(DB_SQL.DICT_QUERY_BY_NAME, new Object[]{name}, new RowMapper<KV>(){
			
			@Override
			public KV mapRow(ResultSet rs, int arg1) throws SQLException {
				KV kv = new KV(rs.getLong("ID"),rs.getInt("EKEY"), rs.getString("EVALUE"));
				d.setChName(rs.getString("MEMO"));
				return kv;
			}
		});
		d.setKvs(list);
		//System.out.println(DB_SQL.DICT_QUERY_BY_NAME+" "+list.size());
		return d;
//		return dMap.get(name);
	}
	
	public static void main(String[] args) {
		DictDao dao = new DictDao();
		Dict d = new Dict();
		//d.setName(name);
		dao.getByName("brand").getKV().get(1);
	}
	/**
	 * 查出品牌、品类的值
	 */
	public Dict getPPPL(String name){
		String sql = "select distinct id,ekey,evalue from tb_casc where name = ? ";
		final Dict d = new Dict();
		d.setName(name);
		List<KV> list = jdbcTemplate.query(sql, new Object[]{name}, new RowMapper<KV>(){
			@Override
			public KV mapRow(ResultSet rs, int arg1) throws SQLException {
				KV kv = new KV();
				kv.setI(rs.getLong(1));
				kv.setK(rs.getInt(2));
				kv.setV(rs.getString(3));
				return kv;
			}
		});
		d.setKvs(list);
		return d;
	}
	/**
	 * 根据品牌ID和品类名字获取品类ID
	 */
	public int getPLID(int ppid,String plname){
		String sql = "select distinct ekey from tb_casc where pk ="+ppid+"  and evalue ='"+plname+"'" ;
		List<String> list =  jdbcTemplate.query(sql, new RowMapper<String>(){
			@Override
			public String mapRow(ResultSet rs, int arg1) throws SQLException {
				return rs.getString(1);
			}});
		return Integer.parseInt(list.get(0));
	}
	
	
	/**
	 * 根据key查询出Name
	 */
	public Dict getNameByKey(String name,String ekey){
		String sql = "select distinct id,ekey,evalue,memo from tb_casc where name = '"+name+"' and ekey="+ekey;
		final Dict d = new Dict();
		d.setName(name);
		List<KV> list = jdbcTemplate.query(sql, new Object[]{name,ekey}, new RowMapper<KV>(){
			@Override
			public KV mapRow(ResultSet rs, int arg1) throws SQLException {
				KV kv = new KV(rs.getLong("ID"),rs.getInt("EKEY"), rs.getString("EVALUE"));
				d.setChName(rs.getString("memo"));
				return kv;
			}
		});
		d.setKvs(list);
		return d;
	}
	
	
	
	/**
	 * 列举字典名
	 * @return
	 */
	public List<String> listNames(){
		return jdbcTemplate.query(DB_SQL.DICT_QUERY_ALL_NAMES, new RowMapper<String>(){

			@Override
			public String mapRow(ResultSet rs, int arg1) throws SQLException {
				return rs.getString(1);
			}});
	}

	public int deleteByName(String name){
		try{
			return jdbcTemplate.update(DB_SQL.DICT_DEL_BY_NAME, name);
		}finally{
//			initDict();
		}
	}
	
	public int add(Dict d){
		
		List<Object[]> list = new ArrayList<Object[]>();
		for(KV kv : d.getKvs()){
			list.add(new Object[]{d.getName(),kv.getK(), kv.getV(),d.getChName()});
		}

		int[] ia = jdbcTemplate.batchUpdate(DB_SQL.DICT_INS, list);
		
//		initDict();

		return ia.length;
	}

	/**
	 * 
	 * @param d
	 * @return
	 */
	public int update(Dict d){
		List<Object[]> uplist = new ArrayList<Object[]>();
		for(KV kv : d.getKvs()){
			uplist.add(new Object[]{d.getName(),kv.getK(), kv.getV(),d.getChName()});
		}
		jdbcTemplate.update("delete from tb_dict where name='"+d.getName()+"'");
		int[] iu = jdbcTemplate.batchUpdate(DB_SQL.DICT_INS, uplist);

		return iu.length ;
	}
	
	
	/**
     * 查询级联字典
     * @param name
     * @return
     */
	public Dict getCascByName(String name) {
		final Dict d = new Dict();
		d.setName(name);
		String sql="SELECT EKEY,EVALUE FROM TB_CASC WHERE NAME=?";
		List<KV> list = jdbcTemplate.query(sql, new Object[]{name}, new RowMapper<KV>(){
			@Override
			public KV mapRow(ResultSet rs, int arg1) throws SQLException {
				KV kv = new KV(rs.getInt("EKEY"), rs.getString("EVALUE"));
				return kv;
			}
		});
		d.setKvs(list);
		return d;
	}
	/**
    * 列举字典中英文名
    * */
	public Dict[] listCHName (){
	    List<Dict>  dicList =jdbcTemplate.query(DB_SQL.DICT_QUERY_ALL_NAMES, new RowMapper<Dict>(){
			@Override
			public Dict mapRow(ResultSet rs, int index) throws SQLException {
				Dict dict = new Dict();
				dict.setName(rs.getString(1));
				dict.setChName(rs.getString(2));
				return dict;
			}});
	    //System.out.println(dicList.size()+"----1");
	    Dict[] dicts = new Dict[0];
	    dicts = dicList.toArray(dicts);
		return dicts;
	}
}
